﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IBLL;
using MisDLL;
using System.Data;

namespace MisBLL
{
    public class CusDepDAO : MarshalByRefObject, ICusDep
    {
        protected DbHelper dbHelper = new DbHelper();

        protected MisEntity.DataEntityDataContext Ctx = new MisEntity.DataEntityDataContext();

        //根据编码查询客户部门代码信息
        public MisModel.CusDep GetById(string id)
        {
            if (string.IsNullOrEmpty(id))
                return null;
            string sql = string.Format("select * from CusDep_TAB where CUS_ID='{0}'", id);
            DataTable dt=  dbHelper.ExecuteDataTable(sql);
            MisModel.CusDep entity = null;
            if (dt != null && dt.Rows.Count > 0){
                entity = new MisModel.CusDep();
                entity.CusId = dt.Rows[0]["CUS_ID"].ToString();
                entity.CusDep_ = dt.Rows[0]["CUS_DEP"].ToString();
                entity.Depart = dt.Rows[0]["DEPART"].ToString();
                entity.F1 = dt.Rows[0]["F1"].ToString();
                entity.DateE = DateTime.Parse(dt.Rows[0]["DATE_E"].ToString());
                entity.Oper = dt.Rows[0]["OPER"].ToString();
                entity.Tele = dt.Rows[0]["TELE"].ToString();
                entity.Jebz1 = decimal.Parse(dt.Rows[0]["jebz1"].ToString());
                entity.Jebz2 = decimal.Parse(dt.Rows[0]["jebz2"].ToString());
            }
            return entity;
        }

        //根据编码查询客户部门代码信息
        public MisModel.CusDep GetBy(string id, string dep)
        {
            if (string.IsNullOrEmpty(id))
                return null;
            string sql = string.Format("select * from CusDep_TAB where CUS_ID='{0}' and CUS_DEP='{1}'", id,dep);
            DataTable dt = dbHelper.ExecuteDataTable(sql);
            MisModel.CusDep entity = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                entity = new MisModel.CusDep();
                entity.CusId = dt.Rows[0]["CUS_ID"].ToString();
                entity.CusDep_ = dt.Rows[0]["CUS_DEP"].ToString();
                entity.Depart = dt.Rows[0]["DEPART"].ToString();
                entity.F1 = dt.Rows[0]["F1"].ToString();
                entity.DateE = DateTime.Parse(dt.Rows[0]["DATE_E"].ToString());
                entity.Oper = dt.Rows[0]["OPER"].ToString();
                entity.Tele = dt.Rows[0]["TELE"].ToString();
                entity.Jebz1 = decimal.Parse(dt.Rows[0]["jebz1"].ToString());
                entity.Jebz2 = decimal.Parse(dt.Rows[0]["jebz2"].ToString());
            }
            return entity;
        }

        //查询客户部门代码列表
        public DataTable GetAll()
        {
            string sql = "select *,(CUS_ID + '  ' + CUS_DEP + '  ' + F1+ '  ' + DEPART) as ItemShow  from CusDep_TAB";
            return dbHelper.ExecuteDataTable(sql);
        }

        //查询客户部门代码列表
        public IList<MisEntity.CusDep> FindAll()
        {
            var query = from item in Ctx.CusDep
                        select item;
            if (query.Count<MisEntity.CusDep>() == 0)
                return null;
            return query.ToList<MisEntity.CusDep>();
        }

        //根据客户ID查询所属部门
        public IList<MisEntity.CusDep> FindBy(string CustID)
        {
            var query = from item in Ctx.CusDep
                        where item.CUS_ID == CustID
                        select item;
            if (query.Count<MisEntity.CusDep>() == 0)
                return null;
            return query.ToList<MisEntity.CusDep>();
        }

        //添加客户部门代码信息
        public int Add(MisModel.CusDep obj)
        {
            if (string.IsNullOrEmpty(obj.F1.ToString()))
            {
                obj.F1 = "Y";
            }
            string sql = string.Format("insert into CusDep_TAB(CUS_ID,CUS_DEP,DEPART,F1,DATE_E,OPER,TELE,jebz1,jebz2) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", obj.CusId, obj.CusDep_, obj.Depart, obj.F1, obj.DateE, obj.Oper, obj.Tele, obj.Jebz1, obj.Jebz2);
            return dbHelper.ExecuteNonQuery(sql);
        }

        //修改客户部门代码信息
        public int Update(MisModel.CusDep obj)
        {
            string sql = string.Format("update CusDep_TAB set CUS_DEP='{0}',DEPART='{1}',F1='{2}',DATE_E='{3}',OPER='{4}',TELE='{5}',jebz1='{6}',jebz2='{7}' where CUS_ID='{8}'", obj.CusDep_, obj.Depart, obj.F1, obj.DateE, obj.Oper, obj.Tele, obj.Jebz1, obj.Jebz2, obj.CusId);
            return dbHelper.ExecuteNonQuery(sql);
        }

        //根据ID删除客户部门代码信息
        public int Delete(string id)
        {
            string sql = string.Format("delete from CusDep_TAB where CUS_ID='{0}'", id);
            return dbHelper.ExecuteNonQuery(sql);
        }
    }
}
